package com.skyline.energy.utils;

import java.io.StringWriter;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Calendar;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.skyline.energy.dataaccess.jdbc.ConnectionHolder;
import com.skyline.energy.dataaccess.jdbc.JdbcTransactionContext;
import com.skyline.energy.dataaccess.jdbc.JdbcTransactionManager;
import com.skyline.energy.dataaccess.jdbc.SqlExecuteContextHolder;
import com.skyline.energy.dataaccess.jdbc.SqlType;
import com.skyline.energy.datasource.DistributeDataSource;
import com.skyline.energy.exception.DataAccessException;
import com.skyline.energy.exception.TransactionException;
import com.skyline.energy.transaction.IsolationLevel;
import com.skyline.energy.transaction.TransactionContextHolder;
import com.skyline.energy.transaction.TransactionDefinition;

public class JdbcUtils {
	private static final Log LOGGER = LogFactory.getLog(JdbcUtils.class);

	public static Connection getConnection(DataSource dataSource) {
		try {
			if (dataSource instanceof DistributeDataSource) {
				return doGetConnection(((DistributeDataSource) dataSource).getDataSource(), true);
			} else {
				return doGetConnection(dataSource, false);
			}
		} catch (SQLException ex) {
			throw new DataAccessException("Could not get JDBC Connection", ex);
		}
	}

	public static Connection doGetConnection(DataSource dataSource, boolean distribute) throws SQLException {
		CommonUtils.assertNotNull(dataSource, "No DataSource specified");

		JdbcTransactionContext txObject = (JdbcTransactionContext) TransactionContextHolder.getContext();
		if (txObject == null) {// 不支持事务
			return dataSource.getConnection();
		}

		boolean shouldAddCount = false;
		Connection con = null;
		ConnectionHolder conHolder = txObject.getConnectionHolder();
		if (conHolder != null && conHolder.getCurrentConnection() != null) {// 已经存在事务
			// 判断是否可以使用上次的connection
			if (dataSource.equals(txObject.getTxDataSource())) {// 只对同一数据源进行事务处理
				con = conHolder.getCurrentConnection();
				shouldAddCount = true;
			} else {
				con = dataSource.getConnection();
			}
		} else { // 第一次开始事务
			JdbcTransactionManager txManager = (JdbcTransactionManager) txObject.getTxManager();
			if (distribute) {// 分布式数据源，只在第一此写操作时启动事务
				if (needBeginTransaction()) {
					beginNewTransaction(dataSource, txObject);
					con = txObject.getConnectionHolder().getCurrentConnection();
					shouldAddCount = true;
				} else {// 创建独立于事务之外的connection
					con = dataSource.getConnection();
				}
			} else if (txManager.isLazyBegin()) {// 仅懒启动式事务，第一次启动事务
				beginNewTransaction(dataSource, txObject);
				con = txObject.getConnectionHolder().getCurrentConnection();
				shouldAddCount = true;
			} else {
				throw new TransactionException("not lazyBegin transaction bu no currentConnection exist");
			}

			conHolder = txObject.getConnectionHolder(); // 重新获取ConnectionHolder
		}

		if (shouldAddCount) {
			conHolder.addCounter();
		}

		return con;
	}

	private static void beginNewTransaction(DataSource dataSource, JdbcTransactionContext txObject) {
		Connection con = null;
		try {
			Connection newCon = dataSource.getConnection();
			LOGGER.debug("Acquired Connection [" + newCon + "] for JDBC transaction");
			ConnectionHolder conHolder = new ConnectionHolder(newCon);
			txObject.setConnectionHolder(conHolder);
			txObject.setTxDataSource(dataSource);

			con = conHolder.getCurrentConnection();

			JdbcUtils.prepareConnectionForTransaction(newCon, txObject.getCurrentDefinition());
		} catch (Exception ex) {
			releaseConnection(con);
			throw new TransactionException("Could not open JDBC Connection for transaction", ex);
		}
	}

	private static boolean needBeginTransaction() {
		return (SqlExecuteContextHolder.getContext().getSqlType() == SqlType.UPDATE);
	}

	public static PreparedStatement createPreparedStatement(final Connection con, final String sql,
			final boolean returnKeys, final Object... args) throws SQLException {
		PreparedStatement ps = null;
		if (returnKeys) {
			ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
		} else {
			ps = con.prepareStatement(sql);
		}

		if (args != null) {
			for (int i = 0; i < args.length; i++) {
				Object arg = args[i];
				setParameterValue(ps, i + 1, arg);
			}
		}

		return ps;
	}

	public static void setParameterValue(PreparedStatement ps, int paramIndex, Object inValue) throws SQLException {
		if (LOGGER.isDebugEnabled()) {
			LOGGER.debug("Setting SQL statement parameter value: column index " + paramIndex + ", parameter value ["
					+ inValue + "], value class [" + (inValue != null ? inValue.getClass().getName() : "null") + "]");
		}

		if (inValue == null) {
			setNull(ps, paramIndex);
		} else {
			setValue(ps, paramIndex, inValue);
		}
	}

	private static void setNull(PreparedStatement ps, int paramIndex) throws SQLException {
		boolean useSetObject = false;
		int sqlType = Types.NULL;
		try {
			DatabaseMetaData dbmd = ps.getConnection().getMetaData();
			String databaseProductName = dbmd.getDatabaseProductName();
			String jdbcDriverName = dbmd.getDriverName();
			if (databaseProductName.startsWith("Informix") || jdbcDriverName.startsWith("Microsoft SQL Server")) {
				useSetObject = true;
			} else if (databaseProductName.startsWith("DB2") || jdbcDriverName.startsWith("jConnect")
					|| jdbcDriverName.startsWith("SQLServer") || jdbcDriverName.startsWith("Apache Derby")) {
				sqlType = Types.VARCHAR;
			}
		} catch (Throwable ex) {
			LOGGER.debug("Could not check database or driver name", ex);
		}
		if (useSetObject) {
			ps.setObject(paramIndex, null);
		} else {
			ps.setNull(paramIndex, sqlType);
		}
	}

	private static void setValue(PreparedStatement ps, int paramIndex, Object inValue) throws SQLException {
		if (isTypeString(inValue.getClass())) {
			ps.setString(paramIndex, inValue.toString());
		} else if (isTypeDate(inValue.getClass())) {
			ps.setTimestamp(paramIndex, new Timestamp(((java.util.Date) inValue).getTime()));
		} else if (inValue instanceof Calendar) {
			Calendar cal = (Calendar) inValue;
			ps.setTimestamp(paramIndex, new Timestamp(cal.getTime().getTime()), cal);
		} else {
			// Fall back to generic setObject call without SQL type specified.
			ps.setObject(paramIndex, inValue);
		}
	}

	public static void commitQuietly(Connection con) {
		if (con != null) {
			try {
				if (!con.getAutoCommit()) {
					con.commit();
				}
			} catch (SQLException ex) {
				LOGGER.debug("Could not close JDBC Connection");
			} catch (Throwable ex) {
				// We don't trust the JDBC driver: It might throw RuntimeException or Error.
				LOGGER.debug("Unexpected exception on closing JDBC Connection", ex);
			}
		}
	}

	public static void prepareConnectionForTransaction(Connection con, TransactionDefinition definition)
			throws SQLException {

		CommonUtils.assertNotNull(con, "No Connection specified");

		// Set read-only flag.
		if (definition != null && definition.isReadOnly()) {
			try {
				LOGGER.debug("Setting JDBC Connection [" + con + "] read-only");
				con.setReadOnly(true);
			} catch (SQLException ex) {
				Throwable exToCheck = ex;
				while (exToCheck != null) {
					if (exToCheck.getClass().getSimpleName().contains("Timeout")) {
						// Assume it's a connection timeout that would otherwise get lost: e.g. from JDBC 4.0
						throw ex;
					}
					exToCheck = exToCheck.getCause();
				}
				// "read-only not supported" SQLException -> ignore, it's just a hint anyway
				LOGGER.debug("Could not set JDBC Connection read-only", ex);
			} catch (RuntimeException ex) {
				Throwable exToCheck = ex;
				while (exToCheck != null) {
					if (exToCheck.getClass().getSimpleName().contains("Timeout")) {
						// Assume it's a connection timeout that would otherwise get lost: e.g. from Hibernate
						throw ex;
					}
					exToCheck = exToCheck.getCause();
				}
				// "read-only not supported" UnsupportedOperationException -> ignore, it's just a hint anyway
				LOGGER.debug("Could not set JDBC Connection read-only", ex);
			}
		}

		if (definition != null && definition.getIsolationLevel() != IsolationLevel.ISOLATION_DEFAULT) {
			LOGGER.debug("Changing isolation level of JDBC Connection [" + con + "] to "
					+ definition.getIsolationLevel());
			IsolationLevel currentIsolation = IsolationLevel.toIsolationLevel(con.getTransactionIsolation());
			if (currentIsolation != definition.getIsolationLevel()) {
				con.setTransactionIsolation(definition.getIsolationLevel().toJdbcIsolation());
			}
		}

		if (con.getAutoCommit()) {
			LOGGER.debug("Switching JDBC Connection [" + con + "] to manual commit");
			con.setAutoCommit(false);
		}
	}

	/**
	 * Close the given JDBC Connection and ignore any thrown exception. This is useful for typical finally blocks in
	 * manual JDBC code.
	 * 
	 * @param con
	 *            the JDBC Connection to close (may be <code>null</code>)
	 */
	public static void closeConnection(Connection con) {
		if (con != null) {
			try {
				if (!con.isClosed()) { // 关闭没有关闭
					con.close();
				}
			} catch (SQLException ex) {
				LOGGER.debug("Could not close JDBC Connection", ex);
			} catch (Throwable ex) {
				// We don't trust the JDBC driver: It might throw RuntimeException or Error.
				LOGGER.debug("Unexpected exception on closing JDBC Connection", ex);
			}
		}
	}

	/**
	 * Close the given JDBC Statement and ignore any thrown exception. This is useful for typical finally blocks in
	 * manual JDBC code.
	 * 
	 * @param stmt
	 *            the JDBC Statement to close (may be <code>null</code>)
	 */
	public static void closeStatement(Statement stmt) {
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException ex) {
				LOGGER.trace("Could not close JDBC Statement", ex);
			} catch (Throwable ex) {
				// We don't trust the JDBC driver: It might throw RuntimeException or Error.
				LOGGER.trace("Unexpected exception on closing JDBC Statement", ex);
			}
		}
	}

	/**
	 * Close the given JDBC ResultSet and ignore any thrown exception. This is useful for typical finally blocks in
	 * manual JDBC code.
	 * 
	 * @param rs
	 *            the JDBC ResultSet to close (may be <code>null</code>)
	 */
	public static void closeResultSet(ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException ex) {
				LOGGER.trace("Could not close JDBC ResultSet", ex);
			} catch (Throwable ex) {
				// We don't trust the JDBC driver: It might throw RuntimeException or Error.
				LOGGER.trace("Unexpected exception on closing JDBC ResultSet", ex);
			}
		}
	}

	/**
	 * Return whether the given JDBC driver supports JDBC 2.0 batch updates.
	 * <p>
	 * Typically invoked right before execution of a given set of statements: to decide whether the set of SQL
	 * statements should be executed through the JDBC 2.0 batch mechanism or simply in a traditional one-by-one fashion.
	 * <p>
	 * Logs a warning if the "supportsBatchUpdates" methods throws an exception and simply returns <code>false</code> in
	 * that case.
	 * 
	 * @param con
	 *            the Connection to check
	 * @return whether JDBC 2.0 batch updates are supported
	 * @see java.sql.DatabaseMetaData#supportsBatchUpdates()
	 */
	public static boolean supportsBatchUpdates(Connection con) {
		try {
			DatabaseMetaData dbmd = con.getMetaData();
			if (dbmd != null) {
				if (dbmd.supportsBatchUpdates()) {
					LOGGER.debug("JDBC driver supports batch updates");
					return true;
				} else {
					LOGGER.debug("JDBC driver does not support batch updates");
				}
			}
		} catch (SQLException ex) {
			LOGGER.debug("JDBC driver 'supportsBatchUpdates' method threw exception", ex);
		} catch (AbstractMethodError err) {
			LOGGER.debug("JDBC driver does not support JDBC 2.0 'supportsBatchUpdates' method", err);
		}
		return false;
	}

	public static void releaseConnection(Connection con) {
		if (con != null) {
			JdbcTransactionContext txObject = (JdbcTransactionContext) TransactionContextHolder.getContext();
			if (txObject == null) {// 不支持事务
				closeConnection(con);
			}

			ConnectionHolder conHolder = txObject.getConnectionHolder();
			if (conHolder != null && conHolder.getCurrentConnection() != null) {
				Connection conInTx = conHolder.getCurrentConnection();
				if (conInTx.equals(con)) {// 当前连接和事务处于同一事务，不关闭连接
					conHolder.reduceCounter();
					return;
				}
			}

			// 不在同一事务中，直接关闭连接
			closeConnection(con);

		}

	}

	public static void commitTransaction(Connection con) throws SQLException {
		JdbcTransactionContext txObject = (JdbcTransactionContext) TransactionContextHolder.getContext();
		if (txObject == null) {// 不支持事务
			throw new TransactionException("Transaction not exist.");
		}

		ConnectionHolder conHolder = txObject.getConnectionHolder();
		if (conHolder.isOpen()) {// 还有后续事务，不提交
			return;
		}

		if (con != null) {
			LOGGER.debug("Transaction commit.");
			try {
				con.commit();
			} catch (SQLException e) {
				throw e;
			} finally {
				closeConnection(con);
			}
		}

	}

	public static void rollbackTransaction(Connection con) throws SQLException {
		if (con != null) {
			LOGGER.debug("Transaction rollback.");
			try {
				con.rollback();
			} catch (SQLException e) {
				throw e;
			} finally {
				closeConnection(con);
			}
		}
	}

	public static boolean isTypeString(Class<?> type) {
		// Consider any CharSequence (including StringBuffer and StringBuilder) as a String.
		return (CharSequence.class.isAssignableFrom(type) || StringWriter.class.isAssignableFrom(type));
	}

	/**
	 * Check whether the given value is a <code>java.util.Date</code> (but not one of the JDBC-specific subclasses).
	 */
	public static boolean isTypeDate(Class<?> type) {
		return (java.util.Date.class.isAssignableFrom(type) && !(java.sql.Date.class.isAssignableFrom(type)
				|| java.sql.Time.class.isAssignableFrom(type) || java.sql.Timestamp.class.isAssignableFrom(type)));
	}

	public static String lookupColumnName(ResultSetMetaData resultSetMetaData, int columnIndex) throws SQLException {
		String name = resultSetMetaData.getColumnLabel(columnIndex);
		if (name == null || name.length() < 1) {
			name = resultSetMetaData.getColumnName(columnIndex);
		}
		return name;
	}

	public static Object getResultSetValue(ResultSet rs, int index) throws SQLException {
		Object obj = rs.getObject(index);
		String className = null;
		if (obj != null) {
			className = obj.getClass().getName();
		}
		if (obj instanceof Blob) {
			obj = rs.getBytes(index);
		} else if (obj instanceof Clob) {
			obj = rs.getString(index);
		} else if (className != null
				&& ("oracle.sql.TIMESTAMP".equals(className) || "oracle.sql.TIMESTAMPTZ".equals(className))) {
			obj = rs.getTimestamp(index);
		} else if (className != null && className.startsWith("oracle.sql.DATE")) {
			String metaDataClassName = rs.getMetaData().getColumnClassName(index);
			if ("java.sql.Timestamp".equals(metaDataClassName) || "oracle.sql.TIMESTAMP".equals(metaDataClassName)) {
				obj = rs.getTimestamp(index);
			} else {
				obj = rs.getDate(index);
			}
		} else if (obj != null && obj instanceof java.sql.Date) {
			if ("java.sql.Timestamp".equals(rs.getMetaData().getColumnClassName(index))) {
				obj = rs.getTimestamp(index);
			}
		}
		return obj;
	}

}
